package org.lanlab.storode.dal.repository;

import org.lanlab.storode.dal.entity.Requirement;
import org.springframework.data.jdbc.repository.query.Modifying;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * @author dove
 * @since 2020/1/8 17:50
 */
public interface RequirementRepository extends CrudRepository<Requirement, Long> {

    @Query("SELECT * FROM requirement r " +
            "WHERE requirement_no = :requirementNo AND is_deleted = FALSE ORDER BY version DESC limit 1")
    Requirement findLatestVersion(@Param("requirementNo") String requirementNo);

    @Modifying
    @Query("UPDATE requirement SET is_deleted = true WHERE requirement_no = :requirementNo")
    int deleteAllByRequirementNo(@Param("requirementNo") String requirementNo);

    @Query("SELECT * FROM requirement as rl WHERE version = (SELECT max(version) FROM requirement as rr where rl.requirement_no = rr.requirement_no)")
    List<Requirement> findAllLatestRequirements();
}
